const express = require('express');
const router = express.Router();
const rds = require('../rds.js');
const moment = require('moment');

// 医生业绩折线图路由
router.get('/chart', async (req, res) => {
    try {
                const {
            begin = moment().subtract(12, 'months').format('YYYY-MM-01'),
            end = moment().format('YYYY-MM-DD'),
            limit = 20,  // 限制显示的客户数量
            type = 'first_order',  // 图表类型：first_order(首患), recruited(推荐), sales(销售额)
            exclude_cid = '',  // 排除的客户CID
            show_lines = 'both',  // 显示线条类型：both(默认), average_only(只显示均值线), total_only(只显示合计线)
            rep_code = ''  // 代表代码筛选
        } = req.query;

        // 确保limit是数字类型
        const limitNum = parseInt(limit) || 20;

        // 处理排除的CID
        const excludeCidNum = exclude_cid ? parseInt(exclude_cid) : null;

        // 处理代表代码筛选
        const repCodeFilter = rep_code ? rep_code.trim() : '';

        // 计算时间点
        const endMoment = moment(end);
        const startMoment = moment(begin);

        // 生成指定时间范围内的月份时间戳
        const monthTimestamps = [];
        let currentMonth = startMoment.clone().startOf('month');
        const endMonth = endMoment.clone().startOf('month');

        while (currentMonth.isSameOrBefore(endMonth)) {
            const monthEnd = currentMonth.clone().endOf('month');
            monthTimestamps.push({
                start: currentMonth.unix(),
                end: monthEnd.unix(),
                label: currentMonth.format('YYYY-MM'),
                year: currentMonth.year(),
                month: currentMonth.month() + 1
            });
            currentMonth.add(1, 'month');
        }

                // 根据图表类型确定统计字段和阈值计算
        let statField, totalField, thresholdField;
        let chartTitle, chartDescription;

        switch(type) {
            case 'recruited':
                statField = 'recruited_count';
                totalField = 'total_recruited';
                thresholdField = 'total_recruited';
                chartTitle = '客户推荐患者数量趋势图';
                chartDescription = '过去12个月推荐患者数量占总量80%的客户';
                break;
            case 'sales':
                statField = 'actual_weixin_paid';
                totalField = 'total_sales';
                thresholdField = 'total_sales';
                chartTitle = '客户销售额趋势图';
                chartDescription = '过去12个月销售额占总量80%的客户';
                break;
            default: // first_order
                statField = 'first_order_count';
                totalField = 'total_first_orders';
                thresholdField = 'total_first_orders';
                chartTitle = '客户首患数量趋势图';
                chartDescription = '过去12个月首患数量占总量80%的客户';
                break;
        }

        // 如果指定了代表代码，在标题和描述中添加筛选信息
        if (repCodeFilter) {
            chartTitle += ` (代表: ${repCodeFilter})`;
            chartDescription += ` (代表: ${repCodeFilter})`;
        }

                // 获取总的统计数据，用于计算80%阈值
        let totalQuery = `
            SELECT SUM(r.${statField}) as ${totalField}
            FROM tutem_mall.eb_user u
            LEFT JOIN tutem_mall.eb_spreader_daily_report r ON u.uid = r.spreader_uid
            WHERE (u.promoter_type = 1 OR u.promoter_type = 2 OR u.promoter_type = 5 OR u.promoter_type = 7)
                AND r.noon_time >= UNIX_TIMESTAMP(?)
                AND r.noon_time < UNIX_TIMESTAMP(?)
                AND r.${statField} > 0
        `;

        // 如果指定了排除的CID，添加排除条件
        if (excludeCidNum) {
            totalQuery += ` AND u.uid != ${excludeCidNum}`;
        }

        // 如果指定了代表代码，添加筛选条件
        if (repCodeFilter) {
            totalQuery += ` AND u.rep_code = '${repCodeFilter}'`;
        }

        const [totalResult] = await rds.query(totalQuery, [begin, end]);
        const totalValue = totalResult[0]?.[totalField] || 0;
        const threshold = totalValue * 0.8; // 80%阈值

                        // 获取每个客户的年度总统计数据
        let doctorTotalQuery = `
            SELECT
                u.uid,
                u.real_name,
                u.rep_code,
                u.agency,
                u.department,
                SUM(r.${statField}) as ${totalField}
            FROM tutem_mall.eb_user u
            LEFT JOIN tutem_mall.eb_spreader_daily_report r ON u.uid = r.spreader_uid
            WHERE (u.promoter_type = 1 OR u.promoter_type = 2 OR u.promoter_type = 5 OR u.promoter_type = 7)
                AND r.noon_time >= UNIX_TIMESTAMP(?)
                AND r.noon_time < UNIX_TIMESTAMP(?)
                AND r.${statField} > 0
        `;

        // 如果指定了排除的CID，添加排除条件
        if (excludeCidNum) {
            doctorTotalQuery += ` AND u.uid != ${excludeCidNum}`;
        }

        // 如果指定了代表代码，添加筛选条件
        if (repCodeFilter) {
            doctorTotalQuery += ` AND u.rep_code = '${repCodeFilter}'`;
        }

        doctorTotalQuery += `
            GROUP BY u.uid, u.real_name, u.rep_code, u.agency, u.department
            HAVING ${totalField} > 0
            ORDER BY ${totalField} DESC
            LIMIT ${limitNum}
        `;

        const [doctorTotals] = await rds.query(doctorTotalQuery, [begin, end]);

        if (doctorTotals.length === 0) {
            return res.render('doctor-performance/chart', {
                title: '客户首患数量趋势图',
                description: '暂无数据',
                chartData: null,
                months: monthTimestamps.map(m => m.label)
            });
        }

        // 获取前80%医生的UID列表
        const topDoctorUids = doctorTotals.map(d => d.uid);

        if (topDoctorUids.length === 0) {
            return res.render('doctor-performance/chart', {
                title: '客户首患数量趋势图',
                description: '暂无数据',
                chartData: null,
                months: monthTimestamps.map(m => m.label)
            });
        }

        const uidList = topDoctorUids.join(',');

        // 构建每月首患数量查询
        let monthlyQuery = `
            SELECT
                u.uid,
                u.real_name,
                u.rep_code,
                u.agency,
                u.department,
        `;

        // 为每个月添加查询条件
        monthTimestamps.forEach((month, index) => {
            monthlyQuery += `
                COALESCE(SUM(CASE WHEN r.noon_time >= ${month.start} AND r.noon_time < ${month.end} THEN r.${statField} ELSE 0 END), 0) AS month_${index + 1},
            `;
        });

        monthlyQuery += `
                SUM(r.${statField}) as ${totalField}
            FROM tutem_mall.eb_user u
            LEFT JOIN tutem_mall.eb_spreader_daily_report r ON u.uid = r.spreader_uid
            WHERE u.uid IN (${uidList})
                AND r.noon_time >= UNIX_TIMESTAMP(?)
                AND r.noon_time < UNIX_TIMESTAMP(?)
            GROUP BY u.uid, u.real_name, u.rep_code, u.agency, u.department
            ORDER BY ${totalField} DESC
        `;

        const [monthlyData] = await rds.query(monthlyQuery, [begin, end]);

        // 处理数据用于图表显示
        const chartData = monthlyData.map(doctor => {
            const monthlyValues = [];
            for (let i = 1; i <= monthTimestamps.length; i++) {
                monthlyValues.push(parseInt(doctor[`month_${i}`]) || 0);
            }

            // 保护隐私：只显示C#uid和姓，机构名称只保留前两个汉字
            const lastName = doctor.real_name ? doctor.real_name.charAt(0) : '';
            const displayName = `C#${doctor.uid}${lastName}`;

            // 处理机构名称，只保留前两个汉字
            let agencyDisplay = '';
            if (doctor.agency) {
                const agencyName = doctor.agency.trim();
                if (agencyName.length >= 2) {
                    agencyDisplay = agencyName.substring(0, 2) + '***';
                } else {
                    agencyDisplay = agencyName + '***';
                }
            }

            return {
                name: `${displayName} (${doctor.rep_code})`,
                uid: doctor.uid,
                rep_code: doctor.rep_code,
                agency: agencyDisplay,
                department: doctor.department,
                total: parseInt(doctor[totalField]) || 0,
                data: monthlyValues
            };
        });

        // 计算均值线（如果指定了排除CID，则排除该客户；否则不排除任何客户）
        const clientsForAverage = excludeCidNum ? chartData.filter(client => client.uid !== excludeCidNum) : chartData;

        // 计算均值线数据
        let averageLineData = [];
        if (clientsForAverage.length > 0) {
            // 计算每个月的平均值
            for (let month = 0; month < monthTimestamps.length; month++) {
                const monthValues = clientsForAverage.map(client => client.data[month]);
                const sum = monthValues.reduce((acc, val) => acc + val, 0);
                const average = sum / clientsForAverage.length;
                averageLineData.push(Math.round(average * 100) / 100); // 保留两位小数
            }
        } else {
            // 如果没有其他客户数据，均值线为0
            averageLineData = new Array(monthTimestamps.length).fill(0);
        }

        // 计算合计线数据（如果指定了排除CID，则排除该客户；否则包含所有客户）
        let totalLineData = [];
        const clientsForTotal = excludeCidNum ? chartData.filter(client => client.uid !== excludeCidNum) : chartData;
        for (let month = 0; month < monthTimestamps.length; month++) {
            const monthValues = clientsForTotal.map(client => client.data[month]);
            const sum = monthValues.reduce((acc, val) => acc + val, 0);
            totalLineData.push(sum);
        }

        // 计算累计百分比
        let cumulativePercentage = 0;
        const clientWithPercentage = chartData.map(client => {
            cumulativePercentage += (client.total / totalValue) * 100;
            return {
                ...client,
                percentage: (client.total / totalValue) * 100,
                cumulativePercentage: cumulativePercentage
            };
        });

        const months = monthTimestamps.map(m => m.label);
        const totalValueFormatted = totalValue.toLocaleString();
        const thresholdFormatted = threshold.toLocaleString();

        res.render('doctor-performance/chart', {
            title: chartTitle,
            description: `${chartDescription} (${begin} 至 ${end})`,
            chartData: clientWithPercentage,
            averageLineData: averageLineData,
            totalLineData: totalLineData,
            months: months,
            totalValue: totalValueFormatted,
            threshold: thresholdFormatted,
            clientCount: clientWithPercentage.length,
            begin,
            end,
            limit: limitNum,
            type: type,
            exclude_cid: exclude_cid,
            show_lines: show_lines,
            rep_code: rep_code
        });

    } catch (error) {
        console.error('客户业绩图表查询错误:', error);
        res.status(500).render('warn', {
            title: '查询错误',
            message: '获取客户业绩数据时发生错误',
            error: error.message
        });
    }
});

// 客户业绩详情路由
router.get('/detail/:uid', async (req, res) => {
    try {
        const { uid } = req.params;
        const { begin = moment().subtract(12, 'months').format('YYYY-MM-01'), end = moment().format('YYYY-MM-DD') } = req.query;

        // 获取客户基本信息
        const clientQuery = `
            SELECT uid, real_name, rep_code, agency, department, phone
            FROM tutem_mall.eb_user
            WHERE uid = ?
        `;
        const [clientResult] = await rds.query(clientQuery, [uid]);

        if (clientResult.length === 0) {
            return res.status(404).render('warn', {
                title: '客户不存在',
                message: '未找到指定的客户信息'
            });
        }

        const client = clientResult[0];

        // 获取客户每月详细数据
        const monthlyDetailQuery = `
            SELECT
                DATE_FORMAT(FROM_UNIXTIME(r.noon_time), '%Y-%m') as month,
                SUM(r.recruited_count) as recruited_count,
                SUM(r.first_order_count) as first_order_count,
                SUM(r.actual_weixin_paid) as actual_weixin_paid,
                COUNT(DISTINCT DATE(FROM_UNIXTIME(r.noon_time))) as active_days
            FROM tutem_mall.eb_spreader_daily_report r
            WHERE r.spreader_uid = ?
                AND r.noon_time >= UNIX_TIMESTAMP(?)
                AND r.noon_time < UNIX_TIMESTAMP(?)
            GROUP BY DATE_FORMAT(FROM_UNIXTIME(r.noon_time), '%Y-%m')
            ORDER BY month ASC
        `;

        const [monthlyDetails] = await rds.query(monthlyDetailQuery, [uid, begin, end]);

        // 保护隐私：只显示C#uid和姓，机构名称只保留前两个汉字
        const lastName = client.real_name ? client.real_name.charAt(0) : '';
        const displayName = `C#${client.uid}${lastName}`;

        // 处理机构名称，只保留前两个汉字
        let agencyDisplay = '';
        if (client.agency) {
            const agencyName = client.agency.trim();
            if (agencyName.length >= 2) {
                agencyDisplay = agencyName.substring(0, 2) + '***';
            } else {
                agencyDisplay = agencyName + '***';
            }
        }

        res.render('doctor-performance/detail', {
            title: `客户详情 - ${displayName}`,
            client: {
                ...client,
                displayName: displayName,
                agencyDisplay: agencyDisplay
            },
            monthlyDetails: monthlyDetails,
            begin,
            end
        });

    } catch (error) {
        console.error('客户详情查询错误:', error);
        res.status(500).render('warn', {
            title: '查询错误',
            message: '获取客户详情时发生错误',
            error: error.message
        });
    }
});

// 测试路由
router.get('/test', (req, res) => {
    res.render('doctor-performance/test', {
        title: '客户业绩系统测试'
    });
});

module.exports = router;